from typing import Dict, Any, Optional, List, Type, TypeVar, Generic, Union, Tuple
from sqlalchemy.ext.declarative import DeclarativeMeta

T = TypeVar('T')

class DBServiceInterface(Generic[T]):
    """数据库服务接口，定义所有数据库服务必须实现的方法"""
    
    def save_entity(self, entity_class: Type[T], entity_data: Dict[str, Any], 
                   primary_key_value: Any = None, primary_key_field: str = None) -> Optional[Dict[str, Any]]:
        """保存实体到数据库"""
        raise NotImplementedError
    
    def save_entity_by_filter(self, entity_class: Type[T], entity_data: Dict[str, Any], 
                             filters: Dict[str, Any] = None) -> Optional[Dict[str, Any]]:
        """通过条件保存实体到数据库"""
        raise NotImplementedError
    
    def save_entities(self, entity_class: Type[T], entities_data: List[Dict[str, Any]],
                     primary_key_field: str = None) -> List[Optional[Dict[str, Any]]]:
        """批量保存多个实体到数据库"""
        raise NotImplementedError
    
    def get_entity(self, entity_class: Type[T], primary_key_value: Any, 
                  primary_key_field: str = None) -> Optional[T]:
        """获取实体"""
        raise NotImplementedError
    
    def get_entity_as_dict(self, entity_class: Type[T], primary_key_value: Any, 
                          primary_key_field: str = None) -> Optional[Dict[str, Any]]:
        """获取实体并转换为字典"""
        raise NotImplementedError
    
    def delete_entity(self, entity_class: Type[T], primary_key_value: Any, 
                     primary_key_field: str = None) -> bool:
        """删除实体"""
        raise NotImplementedError
    
    def delete_entities_by_filter(self, entity_class: Type[T], filters: Dict[str, Any]) -> int:
        """按条件删除多个实体"""
        raise NotImplementedError
    
    def query_entities(self, entity_class: Type[T], filters: Dict[str, Any] = None, 
                      limit: int = None, offset: int = None, 
                      order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> List[T]:
        """查询实体列表
        
        Args:
            entity_class: 实体类
            filters (Dict[str, Any] | List[Dict], optional): 过滤条件。支持统一结构和传统结构：
            
            **统一结构（推荐）**：
            {
                "conditions": [
                    {"type": "field", "field": "field_name", "condition": "value"},
                    {"type": "group", "conditions": [...], "logic": "and"|"or"},
                    {"type": "keyword", "value": "keyword", "fields": ["field1", "field2"]}
                ],
                "logic": "and"|"or"
            }
            
            **传统结构（兼容）**：
            - 基础条件：{"field_name": "value"}
            - 操作符条件：{"field_name": {"operator": "value"}}
            - 同字段多条件：{"field_name": {"conditions": [...], "logic": "and"|"or"}}
            - 同字段多条件（旧语法）：{"field_name": ["value1", "value2"]}  # AND关系
            - OR条件：{"_or": [{"field1": "value1"}, {"field2": "value2"}]}
            - 关键词搜索：{"_keyword": {"value": "keyword", "fields": ["field1", "field2"]}}
            
            **示例**：
            
            统一结构示例：
            {
                "conditions": [
                    {"type": "field", "field": "status", "condition": "active"},
                    {
                        "type": "group",
                        "conditions": [
                            {"type": "field", "field": "category", "condition": "tech"},
                            {"type": "field", "field": "category", "condition": "science"}
                        ],
                        "logic": "or"
                    },
                    {"type": "keyword", "value": "AI", "fields": ["title", "content"]}
                ],
                "logic": "and"
            }
            
            传统结构示例：
            {
                "status": "active",
                "category": {"conditions": ["tech", "science"], "logic": "or"},
                "_or": [{"priority": "high"}, {"urgent": True}],
                "_keyword": {"value": "AI", "fields": ["title", "content"]}
            }
            limit: 限制返回数量
            offset: 跳过记录数
            order_by: 排序条件，支持以下格式：
                - 字符串: "field_name" (默认正序)
                - 元组: ("field_name", "asc"|"desc")
                - 列表: [("field1", "asc"), ("field2", "desc"), "field3"]
                - 类型转换: "field_name::int", ("field_name::float", "desc")
                  支持的类型: int/integer, float/real/numeric, str/string/text
            
        Returns:
            实体列表
        """
        raise NotImplementedError
    
    def query_entities_as_dicts(self, entity_class: Type[T], filters: Dict[str, Any] = None, 
                              limit: int = None, offset: int = None, 
                              order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> List[Dict[str, Any]]:
        """查询实体列表并转换为字典列表
        
        Args:
            entity_class: 实体类
            filters: 过滤条件字典，支持以下格式：
                - 精确匹配: {"field_name": "value"}
                - LIKE 通配符: {"field_name": {"like": "pattern%"}}
                - ILIKE 不区分大小写: {"field_name": {"ilike": "pattern%"}}
                - 比较操作符: {"field_name": {"op": ">", "value": 10}}
                - IN 操作: {"field_name": {"op": "in", "value": [1, 2, 3]}}
                - NOT IN 操作: {"field_name": {"op": "not_in", "value": [1, 2, 3]}}
                - NULL 检查: {"field_name": {"op": "is_null"}}
                - NOT NULL 检查: {"field_name": {"op": "is_not_null"}}
                - OR 条件: {"_or": [{"field1": "value1"}, {"field2": {"like": "pattern%"}}]}
                - 关键词多字段搜索: {"_keyword": {"value": "search_term", "fields": ["field1", "field2"]}}
            limit: 限制返回数量
            offset: 跳过记录数
            order_by: 排序条件，支持以下格式：
                - 字符串: "field_name" (默认正序)
                - 元组: ("field_name", "asc"|"desc")
                - 列表: [("field1", "asc"), ("field2", "desc"), "field3"]
        """
        raise NotImplementedError
    
    def count_entities(self, entity_class: Type[T], filters: Dict[str, Any] = None) -> int:
        """统计实体数量
        
        Args:
            entity_class: 实体类
            filters: 过滤条件字典，格式同query_entities方法
            
        Returns:
            符合条件的实体数量
        """
        raise NotImplementedError
    
    def query_entities_as_dicts_by_page(self, entity_class: Type[T], 
                                       filters: Dict[str, Any] = None,
                                       page: int = 1, page_size: int = 10,
                                       order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> Dict[str, Any]:
        """分页查询实体列表并返回完整分页信息
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，支持以下格式：
                - 精确匹配: {字段名: 值}
                - LIKE 通配符: {字段名: {"like": "pattern"}}
                - ILIKE 不区分大小写: {字段名: {"ilike": "pattern"}}
                - 其他操作符: {字段名: {"op": ">", "value": 100}}
                - OR 条件: {"_or": [{"field1": "value1"}, {"field2": {"like": "pattern%"}}]}
                - 关键词多字段搜索: {"_keyword": {"value": "search_term", "fields": ["field1", "field2"]}}
                - 统一多条件语法: {字段名: {"conditions": [...], "logic": "and"|"or"}}
                - 兼容旧语法: {字段名: [{"op": "in", "value": [1]}, {"op": "not_in", "value": [2]}]} (默认AND)
            page: 页码，从1开始
            page_size: 每页大小
            order_by: 排序条件，格式与query_entities相同
            
        Returns:
            包含分页信息的字典：
            {
                "items": [...],        # 当前页数据
                "total": 100,          # 总记录数
                "page": 1,             # 当前页码
                "page_size": 10,       # 每页大小
                "total_pages": 10      # 总页数
            }
            
        示例:
            # 统一语法 - class1既包含1，又不包含2 (AND关系)
            filters = {
                "class1": {
                    "conditions": [
                        {"op": "in", "value": [1]},
                        {"op": "not_in", "value": [2]}
                    ],
                    "logic": "and"
                }
            }
            
            # 统一语法 - class1包含1，或者不包含2 (OR关系)
            filters = {
                "class1": {
                    "conditions": [
                        {"op": "in", "value": [1]},
                        {"op": "not_in", "value": [2]}
                    ],
                    "logic": "or"
                }
            }
        """
        raise NotImplementedError
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        raise NotImplementedError